#!/bin/bash
# dws_1d_to_dws_td.sh all/表名 [日期]
# 1、判断参数是否传入
if [ $# -lt 1 ]; then
    echo "必须输入 all/表名"
    exit 1
fi

table_name=$1
# 2、判断日期是否传入
[ "$2" ] && date=$2 || date=$(date -d '-1 day' +%F)

# 验证日期格式是否为 YYYY-MM-DD
if ! date -d "$date" +%Y-%m-%d >/dev/null 2>&1; then
    echo "日期格式错误，请使用 YYYY-MM-DD 格式"
    exit 1
fi

# 定义函数来执行 SQL 并记录详细日志
execute_sql() {
    local sql=$1
    local table=$2
    echo "开始为表 $table 执行 SQL: $sql"
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    local status=$?
    if [ $status -ne 0 ]; then
        echo "执行 SQL 失败，表 $table: $sql"
        exit 1
    fi
    echo "表 $table 的 SQL 执行成功"
}

# 生成交易域医生粒度问诊历史至今汇总表每日装载 SQL
generate_doctor_consultation_td_daily_sql() {
    local yesterday=$(date -d "$date -1 day" +%Y-%m-%d)
    cat <<EOF
insert overwrite table dws_trade_doctor_consultation_td
    partition (dt = '$date')
select nvl(old.doctor_id, new.doctor_id)                              doctor_id,
       nvl(old.doctor_name, new.doctor_name)                          doctor_name,
       if(old.doctor_id is null, '$date', first_consultation_dt) first_consultation_dt
from (select doctor_id,
             doctor_name,
             first_consultation_dt
      from dws_trade_doctor_consultation_td
      where dt = '$yesterday') old
         full outer join
     (select doctor_id,
             doctor_name
      from dws_trade_doctor_consultation_1d
      where dt = '$date') new
     on old.doctor_id = new.doctor_id
         and old.doctor_name = new.doctor_name;
EOF
}

# 生成互动域医院用户粒度用户评价历史至今汇总表每日装载 SQL
generate_hospital_user_review_td_daily_sql() {
    local yesterday=$(date -d "$date -1 day" +%Y-%m-%d)
    cat <<EOF
insert overwrite table dws_interaction_hospital_user_review_td
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       user_id,
       username,
       min(first_review_dt) first_review_dt
from (select hospital_id,
             hospital_name,
             user_id,
             username,
             first_review_dt
      from dws_interaction_hospital_user_review_td
      where dt = '$yesterday'
      union
      select hospital_id,
             name hospital_name,
             user_id,
             username,
             '$date' first_review_dt
      from (select hospital_id,
                   user_id,
                   '$date' first_review_dt
            from (select doctor_id,
                         user_id
                  from dwd_interaction_review_inc
                  where dt = '$date') review
                     left join (select id,
                                       hospital_id
                                from dim_doctor_full) doc
                               on review.doctor_id = doc.id
            group by user_id,
                     hospital_id) avg
               left join (select id,
                                 name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on avg.hospital_id = hos.id
               left join (select id,
                                 username
                          from dim_user_full
                          where dt = '$date') \`user\`
                         on avg.user_id = \`user\`.id) \`all\`
group by hospital_id,
         hospital_name,
         user_id,
         username;
EOF
}

# 生成互动域医院粒度用户评价历史至今汇总表每日装载 SQL
generate_hospital_review_td_daily_sql() {
    local yesterday=$(date -d "$date -1 day" +%Y-%m-%d)
    cat <<EOF
insert overwrite table dws_interaction_hospital_review_td
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       sum(review_count)      review_count,
       sum(good_review_count) good_review_count
from (select hospital_id,
             hospital_name,
             review_count,
             good_review_count
      from dws_interaction_hospital_review_td
      where dt = '$yesterday'
      union
      select hospital_id,
             name hospital_name,
             review_count,
             good_review_count
      from (select hospital_id,
                   count(*)                  review_count,
                   sum(if(rating = 5, 1, 0)) good_review_count
            from (select doctor_id,
                         rating
                  from dwd_interaction_review_inc
                  where dt = '$date') review
                     left join (select id,
                                       hospital_id
                                from dim_doctor_full
                                where dt = '$date') doc
            group by hospital_id) avg
               left join (select id,
                                 name
                          from dim_hospital_full
                          where dt = '$date') hos
                         on hospital_id = hos.id) \`all\`
group by hospital_id,
         hospital_name;
EOF
}

# 封装执行 SQL 的公共逻辑
execute_table_sql() {
    local table=$1
    local sql_generator=$2
    local sql=$($sql_generator)
    execute_sql "$sql" "$table"
}

# 根据表名匹配加载数据
case $table_name in
    "all")
        execute_table_sql "dws_trade_doctor_consultation_td" "generate_doctor_consultation_td_daily_sql"
        execute_table_sql "dws_interaction_hospital_user_review_td" "generate_hospital_user_review_td_daily_sql"
        execute_table_sql "dws_interaction_hospital_review_td" "generate_hospital_review_td_daily_sql"
        ;;
    "dws_trade_doctor_consultation_td")
        execute_table_sql "$table_name" "generate_doctor_consultation_td_daily_sql"
        ;;
    "dws_interaction_hospital_user_review_td")
        execute_table_sql "$table_name" "generate_hospital_user_review_td_daily_sql"
        ;;
    "dws_interaction_hospital_review_td")
        execute_table_sql "$table_name" "generate_hospital_review_td_daily_sql"
        ;;
    *)
        echo "不支持的表名: $table_name"
        exit 1
        ;;
esac

echo "每日数据加载完成"